import os
import sqlite3
from flask import Flask, render_template, request, redirect, url_for, session, flash, g
from werkzeug.security import generate_password_hash, check_password_hash
import datetime

# 初始化Flask应用
app = Flask(__name__)
app.secret_key = 'your_secret_key_here'  # 用于会话管理的密钥
DATABASE = 'shopping.db'  # SQLite数据库文件名

# 数据库连接函数
def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row  # 使查询结果可以通过列名访问
    return db

# 关闭数据库连接
@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# 初始化数据库
def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()

# 执行数据库查询
def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv

# 执行数据库修改
def modify_db(query, args=()):
    conn = get_db()
    cur = conn.execute(query, args)
    conn.commit()
    cur.close()
    return cur.lastrowid

# 首页路由
@app.route('/')
def index():
    # 获取所有商品
    products = query_db('SELECT * FROM products')
    return render_template('index.html', products=products)

# 商品详情页
@app.route('/product/<int:product_id>')
def product_detail(product_id):
    product = query_db('SELECT * FROM products WHERE id = ?', [product_id], one=True)
    if product is None:
        flash('商品不存在')
        return redirect(url_for('index'))
    return render_template('product_detail.html', product=product)

# 注册路由
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']
        password = request.form['password']
        
        # 检查用户名是否已存在
        existing_user = query_db('SELECT * FROM users WHERE username = ?', [username], one=True)
        if existing_user:
            flash('用户名已存在')
            return redirect(url_for('register'))
        
        # 检查邮箱是否已存在
        existing_email = query_db('SELECT * FROM users WHERE email = ?', [email], one=True)
        if existing_email:
            flash('邮箱已被注册')
            return redirect(url_for('register'))
        
        # 哈希密码并存储用户
        hashed_password = generate_password_hash(password, method='pbkdf2:sha256')
        user_id = modify_db(
            'INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)',
            [username, email, hashed_password]
        )
        
        # 注册成功后自动登录
        session['user_id'] = user_id
        session['username'] = username
        flash('注册成功！')
        return redirect(url_for('index'))
    
    return render_template('register.html')

# 登录路由
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        # 查找用户
        user = query_db('SELECT * FROM users WHERE username = ?', [username], one=True)
        if user and check_password_hash(user['password_hash'], password):
            # 登录成功
            session['user_id'] = user['id']
            session['username'] = user['username']
            flash('登录成功！')
            return redirect(url_for('index'))
        else:
            flash('用户名或密码错误')
            return redirect(url_for('login'))
    
    return render_template('login.html')

# 登出路由
@app.route('/logout')
def logout():
    session.pop('user_id', None)
    session.pop('username', None)
    flash('已成功登出')
    return redirect(url_for('index'))

# 购物车路由
@app.route('/cart')
def cart():
    if 'user_id' not in session:
        flash('请先登录')
        return redirect(url_for('login'))
    
    # 获取购物车中的商品
    cart_items = query_db('''
        SELECT cart.id, products.id as product_id, products.name, products.price, 
               products.image_url, cart.quantity 
        FROM cart 
        JOIN products ON cart.product_id = products.id 
        WHERE cart.user_id = ?
    ''', [session['user_id']])
    
    # 计算总价
    total = sum(item['price'] * item['quantity'] for item in cart_items)
    
    return render_template('cart.html', cart_items=cart_items, total=total)

# 添加商品到购物车
@app.route('/add_to_cart/<int:product_id>', methods=['POST'])
def add_to_cart(product_id):
    if 'user_id' not in session:
        flash('请先登录')
        return redirect(url_for('login'))
    
    quantity = int(request.form.get('quantity', 1))
    if quantity <= 0:
        flash('数量必须为正数')
        return redirect(url_for('product_detail', product_id=product_id))
    
    # 检查商品是否已在购物车中
    existing_item = query_db('''
        SELECT id, quantity FROM cart 
        WHERE user_id = ? AND product_id = ?
    ''', [session['user_id'], product_id], one=True)
    
    if existing_item:
        # 更新数量
        new_quantity = existing_item['quantity'] + quantity
        modify_db('''
            UPDATE cart SET quantity = ? 
            WHERE id = ?
        ''', [new_quantity, existing_item['id']])
    else:
        # 添加新商品
        modify_db('''
            INSERT INTO cart (user_id, product_id, quantity) 
            VALUES (?, ?, ?)
        ''', [session['user_id'], product_id, quantity])
    
    flash('商品已添加到购物车')
    return redirect(url_for('cart'))

# 从购物车移除商品
@app.route('/remove_from_cart/<int:item_id>')
def remove_from_cart(item_id):
    if 'user_id' not in session:
        flash('请先登录')
        return redirect(url_for('login'))
    
    # 确保商品属于当前用户
    item = query_db('SELECT * FROM cart WHERE id = ? AND user_id = ?', [item_id, session['user_id']], one=True)
    if item:
        modify_db('DELETE FROM cart WHERE id = ?', [item_id])
        flash('商品已从购物车移除')
    else:
        flash('操作失败')
    
    return redirect(url_for('cart'))

# 更新购物车商品数量
@app.route('/update_cart/<int:item_id>', methods=['POST'])
def update_cart(item_id):
    if 'user_id' not in session:
        flash('请先登录')
        return redirect(url_for('login'))
    
    quantity = int(request.form.get('quantity', 1))
    if quantity <= 0:
        return redirect(url_for('remove_from_cart', item_id=item_id))
    
    # 确保商品属于当前用户
    item = query_db('SELECT * FROM cart WHERE id = ? AND user_id = ?', [item_id, session['user_id']], one=True)
    if item:
        modify_db('UPDATE cart SET quantity = ? WHERE id = ?', [quantity, item_id])
        flash('购物车已更新')
    else:
        flash('操作失败')
    
    return redirect(url_for('cart'))

# 结账路由
@app.route('/checkout', methods=['GET', 'POST'])
def checkout():
    if 'user_id' not in session:
        flash('请先登录')
        return redirect(url_for('login'))
    
    # 获取购物车中的商品
    cart_items = query_db('''
        SELECT cart.id, products.id as product_id, products.name, products.price, 
               products.image_url, cart.quantity 
        FROM cart 
        JOIN products ON cart.product_id = products.id 
        WHERE cart.user_id = ?
    ''', [session['user_id']])
    
    if not cart_items:
        flash('购物车为空，无法结账')
        return redirect(url_for('cart'))
    
    # 计算总价
    total = sum(item['price'] * item['quantity'] for item in cart_items)
    
    if request.method == 'POST':
        # 获取收货信息
        address = request.form['address']
        phone = request.form['phone']
        
        # 创建订单
        order_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        order_id = modify_db('''
            INSERT INTO orders (user_id, total_amount, address, phone, order_date, status)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', [session['user_id'], total, address, phone, order_date, 'pending'])
        
        # 添加订单商品
        for item in cart_items:
            modify_db('''
                INSERT INTO order_items (order_id, product_id, quantity, price)
                VALUES (?, ?, ?, ?)
            ''', [order_id, item['product_id'], item['quantity'], item['price']])
        
        # 清空购物车
        modify_db('DELETE FROM cart WHERE user_id = ?', [session['user_id']])
        
        flash('订单创建成功！')
        return redirect(url_for('order_detail', order_id=order_id))
    
    return render_template('checkout.html', cart_items=cart_items, total=total)

# 订单列表
@app.route('/orders')
def orders():
    if 'user_id' not in session:
        flash('请先登录')
        return redirect(url_for('login'))
    
    orders = query_db('''
        SELECT * FROM orders 
        WHERE user_id = ? 
        ORDER BY order_date DESC
    ''', [session['user_id']])
    
    return render_template('orders.html', orders=orders)

# 订单详情
@app.route('/order/<int:order_id>')
def order_detail(order_id):
    if 'user_id' not in session:
        flash('请先登录')
        return redirect(url_for('login'))
    
    # 检查订单是否属于当前用户
    order = query_db('''
        SELECT * FROM orders 
        WHERE id = ? AND user_id = ?
    ''', [order_id, session['user_id']], one=True)
    
    if not order:
        flash('订单不存在')
        return redirect(url_for('orders'))
    
    # 获取订单商品
    order_items = query_db('''
        SELECT order_items.*, products.name, products.image_url 
        FROM order_items
        JOIN products ON order_items.product_id = products.id
        WHERE order_id = ?
    ''', [order_id])
    
    return render_template('order_detail.html', order=order, order_items=order_items)

# 初始化数据库并运行应用
if __name__ == '__main__':
    # 如果数据库不存在，则初始化
    if not os.path.exists(DATABASE):
        init_db()
    # app.run(debug=False)
    app.run(host='0.0.0.0', port=5000, debug=False)
